#pragma once
#include <mysql/mysql.h>
#include "teacher.hpp"

const char *host = "localhost";
const char *user = "caryon";
const char *password = "20233962Myl!";
const char *db = "test";
unsigned int port = 3306;

class DBHelper
{
public:
    DBHelper()
    {
        conn = mysql_init(nullptr);
        if (!mysql_real_connect(conn, host, user, password, db, port, nullptr, 0))
            std::cerr << "MySQL Connect Error: " << mysql_error(conn) << std::endl;
    }
    ~DBHelper()
    {
        if (conn)
            mysql_close(conn);
    }

    void insertTeacher(const Teacher &t)
    {
        std::string sql = "INSERT INTO teacher_management(name, idnum, address, telephone, department, wage, start_work_date, major, position) VALUES('" +
                          t.name + "','" + t.id_card + "','" + t.address + "','" + t.phone + "','" + t.department + "'," + std::to_string(t.salary) +
                          ",'" + t.entry_date + "','" + t.major + "','" + t.title + "')";
        if (mysql_query(conn, sql.c_str()) != 0)
        {
            std::cerr << "Insert Error: " << mysql_error(conn) << std::endl;
        }
        else
        {
            std::cout << "Insert Success\n";
        }
    }
    void queryBySalary(double min, double max)
    {
        std::string sql = "SELECT idnum, name, wage FROM teacher_management WHERE wage BETWEEN " + std::to_string(min) + " AND " + std::to_string(max);
        if (mysql_query(conn, sql.c_str()) == 0)
        {
            MYSQL_RES *res = mysql_store_result(conn);
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)))
            {
                std::cout << "教师编号: " << row[0] << ", 姓名: " << row[1] << ", 工资: " << row[2] << std::endl;
            }
            mysql_free_result(res);
        }
        else
        {
            std::cerr << "Query Error: " << mysql_error(conn) << std::endl;
        }
    }
    void updateTeacherSalary(int id, double salary)
    {
        std::string sql = "UPDATE teacher_management SET wage = " + std::to_string(salary) + " WHERE idnum = " + std::to_string(id);
        if (mysql_query(conn, sql.c_str()) != 0)
        {
            std::cerr << "Update Error: " << mysql_error(conn) << std::endl;
        }
        else
        {
            std::cout << "Update Success\n";
        }
    }
    void deleteTeacher(int id)
    {
        std::string sql = "DELETE FROM teacher_management WHERE idnum = " + std::to_string(id);
        if (mysql_query(conn, sql.c_str()) != 0)
        {
            std::cerr << "Delete Error: " << mysql_error(conn) << std::endl;
        }
        else
        {
            std::cout << "Delete Success\n";
        }
    }
private:
    MYSQL *conn;
};



